﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;
using System.Data.OracleClient;

namespace EMR.GeneralManager
{
    public class ExportHIS 
    { 
        /// <summary> 
        /// 执行SQL语句并返回DataSet 
        /// </summary> 
        /// <param name="Sqlstr">SQL语句</param> 
        /// <returns></returns> 
        public static DataSet ExecuteHIS(String Sqlstr)
        {

            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }


        /// <summary> 
        /// 执行SQL语句并返回DataSet 
        /// </summary> 
        /// <param name="Sqlstr">SQL语句</param> 
        /// <returns></returns> 
        public static DataSet ExecuteHISOracle(String Sqlstr)
        {

            using (OracleConnection connection = new OracleConnection(Set.DATABASE_CONNECTION_HIS))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(Sqlstr, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    connection.Close();
                    MessageException.WriteErorrLog(ex);
                    //throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }


        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteEMR(string SQLString)
        {
            using (OracleConnection connection = new OracleConnection(Set.DATABASE_CONNECTION))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    connection.Close();
                    MessageException.WriteErorrLog(ex);
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
       

        public static bool insertValueWithDt(string sqlHIS,string sqlEMR)
        {
            DataSet ds1;
            if(Set.ConType==0)
                ds1 = ExecuteHISOracle(sqlHIS);
            else
                ds1 = ExecuteHIS(sqlHIS);
            DataTable dataTableHIS = ds1.Tables[0];
            int count = 0 ;
            using (OracleConnection conn = new OracleConnection(Set.DATABASE_CONNECTION))
            {
                try
                {
                    OracleCommand cmd = new OracleCommand(sqlEMR, conn);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    OracleCommandBuilder cb = new OracleCommandBuilder(adapter);
                    DataTable dsNew = new DataTable();
                    count = adapter.Fill(dsNew);
                    for (int i = 0; i < dataTableHIS.Rows.Count; i++)
                    {
                        DataRow dr = dsNew.NewRow();
                        for (int j = 0; j < dataTableHIS.Columns.Count; j++)
                        {
                            dr[dsNew.Columns[j].ColumnName] = dataTableHIS.Rows[i][j];
                        }
                        dsNew.Rows.Add(dr);
                    }
                    count = adapter.Update(dsNew);
                    adapter.UpdateBatchSize = 2000;
                    //adapter.Update(dataTable);
                    return true;
                }
                catch (Exception e)
                {
                    MessageException.WriteErorrString("影响行数：" + count.ToString());
                    MessageException.WriteErorrLog(e);
                    throw;
                }
            }
        }

    }
}
